package com.zbcbms.dao.impl;

import com.zbcbms.common.DaoImplCommon;
import com.zbcbms.dao.UserDao;
import com.zbcbms.entity.User;
import com.zbcbms.entity.chart.UserJoinTimeCountChart;
import com.zbcbms.entity.chart.UserRoleCountChart;
import com.zbcbms.entity.vo.UserVo;
import com.zbcbms.util.JDBCUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * @author fengq
 */
public class UserDaoImpl implements UserDao {

    private Connection connection;

    @Override
    public Boolean insertOne(User entity) {
        connection= JDBCUtil.getConnection();
        String sql="INSERT INTO `user` "+
                "(id,user_number,user_name,role,password,join_date,create_date,update_date,is_deleted)"+
                "value(?,?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, UUID.randomUUID().toString().substring(0,25));
            preparedStatement.setString(2,entity.getUserNumber());
            preparedStatement.setString(3,entity.getUserName());
            preparedStatement.setString(4,entity.getRole());
            preparedStatement.setString(5,entity.getPassword());
            preparedStatement.setDate(6,new Date(entity.getJoinDate().getTime()) );
            preparedStatement.setDate(7,new Date(System.currentTimeMillis()));
            preparedStatement.setDate(8,new Date(System.currentTimeMillis()));
            preparedStatement.setInt(9,0);
            return !preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }

    }

    @Override
    public Boolean update(User entity) {
        connection= JDBCUtil.getConnection();
        String sql="UPDATE  `user`  "+
                "SET user_number=?," +
                " user_name=?," +
                " password=?," +
                " join_date=?," +
                " update_date=?, " +
                " role=? " +
                "where user_number =? " +
                "and is_deleted=0";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,entity.getUserNumber());
            preparedStatement.setString(2,entity.getUserName());
            preparedStatement.setString(3,entity.getPassword());
            preparedStatement.setDate(4,new Date(entity.getJoinDate().getTime()) );
            preparedStatement.setDate(5,new Date(System.currentTimeMillis()));
            preparedStatement.setString(6,entity.getRole());
            preparedStatement.setString(7,entity.getUserNumber());
            return !preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public Boolean deleteById(String number) {
        connection= JDBCUtil.getConnection();
        String sql="update user set is_deleted=? where user_number=? and is_deleted=0 ";
        return DaoImplCommon.deleteByCondition(connection,sql,number,1);

    }

    @Override
    public int deleteByIds(List<String> ids) {
        connection= JDBCUtil.getConnection();
        return DaoImplCommon.deleteByIds(connection,ids,"user","user_number");
    }

    @Override
    public long selectCount(String args) {
        connection=JDBCUtil.getConnection();
        return DaoImplCommon.selectCount(connection,"user",null,null);
    }

    @Override
    public Boolean pjySicsDeleteById(String id) {
        connection=JDBCUtil.getConnection();
        String sql="delete from user where user_number= ?";
        return DaoImplCommon.physicalDeleteByCondition(connection,sql,id);
    }

    @Override
    public Boolean recoverById(String id) {
        connection=JDBCUtil.getConnection();
        String sql="update user set is_deleted=0 where user_number=? and is_deleted=1 ";
        return DaoImplCommon.recoverById(connection,sql,id);
    }

    @Override
    public UserVo selectOneById(String number) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="select user_number,user_name,join_date ,role " +
                " from `user` where is_deleted=0 and user_number=? ";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,number);
            ResultSet resultSet = preparedStatement.executeQuery();
            UserVo userVo=null;
            while (resultSet.next()){
                userVo=new UserVo();
                userVo.setUserNumber(resultSet.getString("user_number"));
                userVo.setUserName(resultSet.getString("user_name"));
                userVo.setJoinDate(resultSet.getDate("join_date"));
                userVo.setRole(resultSet.getString("role"));
            }
            return userVo;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<UserVo> selectAllUser() {
        connection=JDBCUtil.getConnection();
        String sql="select user_number,user_name,join_date,role " +
                "from `user` where is_deleted=0 ";
        return DaoImplCommon.selectAllUser(connection,sql);
    }

    @Override
    public Boolean changePasswordById(String id,String password) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="update user set password=? where id=? and is_deleted=0";
        try {
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            preparedStatement.setString(2,password);
            return preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }

    }

    @Override
    public String selectPasswordByNumber(String number) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="select password from `user` where user_number=? and is_deleted=0";
        try {
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1,number);
            ResultSet resultSet = preparedStatement.executeQuery();
            String password=null;
            while(resultSet.next()){
                password=resultSet.getString("password");
            }
            return password;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<String> selectAllUserNumber() {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="select user_number from `user` where is_deleted=0";
        try {
            preparedStatement= connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<String> list=new ArrayList<>();
            while(resultSet.next()){
                list.add(resultSet.getString("user_number"));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<String> selectUserNumberByKeyword(String keyword) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            if (keyword==null||keyword==""){
                String sql="select user_number from `user` where is_deleted=0";
                preparedStatement= connection.prepareStatement(sql);
                resultSet= preparedStatement.executeQuery();
            }else {
                String sql="select user_number from `user` where is_deleted=0 and user_number like ?";
                preparedStatement= connection.prepareStatement(sql);
                preparedStatement.setString(1,"%"+keyword+"%");
                resultSet= preparedStatement.executeQuery();
            }

            List<String> list=new ArrayList<>();
            while(resultSet.next()){
                list.add(resultSet.getString("user_number"));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public String selectUserNameByNumber(String number) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="select user_name from `user` where is_deleted=0 and user_number=? ";
        try {
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1,number);
            ResultSet resultSet = preparedStatement.executeQuery();
            String name=null;
            while(resultSet.next()){
                name=resultSet.getString("user_name");
            }
            return name;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<UserRoleCountChart> selectUserCountByRole() {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="select Count(*) as count ,role from user " +
                "WHERE is_deleted=0 " +
                "GROUP BY role ";
        try {
            preparedStatement= connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<UserRoleCountChart> list=new ArrayList<>();
            while(resultSet.next()){
                UserRoleCountChart userRoleCountChart = new UserRoleCountChart();
                userRoleCountChart.setCount(resultSet.getLong("count"));
                userRoleCountChart.setRole(resultSet.getString("role"));
                list.add(userRoleCountChart);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<UserJoinTimeCountChart> selectUserCountBtJoinTme() {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="select Count(*) as count ,join_date from user " +
                "WHERE is_deleted=0 " +
                "GROUP BY join_date " +
                "ORDER BY join_date desc " +
                "LIMIT 0,20  ";
        try {
            preparedStatement= connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<UserJoinTimeCountChart> list=new ArrayList<>();
            while(resultSet.next()){
                UserJoinTimeCountChart userJoinTimeCountChart = new UserJoinTimeCountChart();
                userJoinTimeCountChart.setCount(resultSet.getLong("count"));
                userJoinTimeCountChart.setJoinDate(resultSet.getDate("join_date"));
                list.add(userJoinTimeCountChart);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<UserVo> selectAllDeletedUser() {
        connection=JDBCUtil.getConnection();
        String sql="select user_number,user_name,join_date,role " +
                "from `user` where is_deleted=1 ";
        return DaoImplCommon.selectAllUser(connection,sql);
    }

    @Override
    public List<UserVo> selectUserVoByKeyWord(String keyword) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="select user_number,user_name,join_date ,role \n" +
                "                from `user` \n" +
                "\t\t\t\t\t\t\t\twhere is_deleted=0 \n" +
                "\t\t\t\t\t\t\t\tand CONCAT(user_number,user_name) like ? \n" +
                "                order BY create_date desc ";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"%"+keyword+"%");
            ResultSet resultSet = preparedStatement.executeQuery();
            List<UserVo> list=new ArrayList<>();
            while (resultSet.next()){
                UserVo userVo=new UserVo();
                userVo.setUserNumber(resultSet.getString("user_number"));
                userVo.setUserName(resultSet.getString("user_name"));
                userVo.setJoinDate(resultSet.getDate("join_date"));
                userVo.setRole(resultSet.getString("role"));
                list.add(userVo);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }


}
